I am trying to teach myself PostgreSQL. I have scant programming background, but a great deal of experience using "off-the-shelf" databases.
It seems there ought to be a way to eliminate the create view, and get the total of the codes found with the queries below. (I've learned that "Aggregate function calls may not be nested," among other things.)
My son, a senior SQL programmer with Teleperformance USA in SLC suggested a union clause; however his (SQL Server) syntax fails. I can't figure how that clause can be applied from the books I have.
Suggestions will be appreciated!
Will also appreciate suggestions on articles which may help me understand the union clause more fully.
Thanks!
mydb=# create view single_code as
select count(city), code from test_year
where length(code) = 1
group by code order by code;
CREATE
mydb=# select * from single_code;
count | code
-------+------
1 | -
1 | 2
2 | A
63 | C
11 | G
224 | H
1 | L
6363 | N
542 | Q
180 | T
820 | V
2 | n
(12 rows)
mydb=# select sum(count) from single_code;
sum
------
8210
(1 row)
Suggested code regarding union clause, with result:
mydb=# select count(city) as qty, code as code
from test_year
where length(code) = 1
group by code
order by code
union all
select count(city) as qty, 'All' as code
from test_year
where length(code) = 1
ERROR: parser: parse error at or near "all"
Drop ALL, and get
ERROR: parser: parse error at or near "select"
--
Forest W. Felling e-mail: forest@oldgrumpy.com
Web: http://www.oldgrumpy.com
Ask me about "Full Spectrum Nutrition!"